package com.affablebean.model.dal;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.affablebean.model.entity.Customer;
import com.affablebean.model.entity.Customer_order;

public class Customer_orderDAL extends AbstractDAL<Customer_order>{

	public Customer_orderDAL() {
		super("CUSTOMER_ORDERS");
	}

	@Override
	public boolean insertEntity(Customer_order entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "INSERT INTO customer_orders VALUES (product_seq.NEXTVAL, ?,?,?,?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd, new String[] { "customer_order_id" })) {
				statement.setBigDecimal(1, entity.getAmount());
				statement.setDate(2, convertToSqlDate(entity.getCreateDate()));
				statement.setInt(3, entity.getCustomer().getId());
				statement.setInt(4, entity.getConfirmation_number());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
						if (generatedKeys.next()) {
							entity.setId(generatedKeys.getInt(1));
						}
					}
				}
			}
		}
		return result;
	}

	@Override
	public boolean updateEntity(Customer_order entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "UPDATE customer_orders SET amount = ?, date_create = ?, customer_id = ?"
					+ ", confirmation_number = ? WHERE customer_order_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setBigDecimal(1, entity.getAmount());
				statement.setDate(2, convertToSqlDate(entity.getCreateDate()));
				statement.setInt(3, entity.getCustomer().getId());
				statement.setInt(4, entity.getConfirmation_number());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		}
		return result;
	}

	@Override
	public boolean deleteEntity(Customer_order entity) throws SQLException {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "DELETE * FROM customer_orders WHERE customer_order_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)){
				statement.setInt(1,entity.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0 ;
			}
		}
		return result;
	}

	@Override
	public List<Customer_order> getAll() throws SQLException {
		List<Customer_order> costumer_order = new ArrayList<Customer_order>();
		try (Connection connection = createNewConnection()) {
			try (Statement statement = connection.createStatement()) {
				final String sqlCmd = "SELECT co.customer_order_id," +
						"co.amount,co.date_created,co.customer_id,co.confirmation_number," +
						"c1.first_name,c1.last_name,c1.email,c1.phone_number,c1.address," +
						"c1.city,c1.cc_number FROM customer_orders co " +
						"INNER JOIN customers c1 ON co.customer_id = c1.customer_id";
				try (ResultSet resultSet = statement.executeQuery(sqlCmd)) {
					while (resultSet.next()) {
						costumer_order.add(createEntity(resultSet));
					}
				}
			}
		}
		return costumer_order;
	}

	@Override
	public Customer_order findById(int id) throws SQLException {
		Customer_order customer_order = null;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "SELECT co.customer_order_id," +
					"co.amount,co.date_created,co.customer_id,co.confirmation_number," +
					"c1.first_name,c1.last_name,c1.email,c1.phone_number,c1.address," +
					"c1.city,c1.cc_number FROM customer_orders co " +
					"INNER JOIN customers c1 ON co.customer_id = c1.customer_id "
					+ "WHERE co.customer_order_id = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				try (ResultSet resultSet = statement.executeQuery()) {
					if (resultSet.next()) {
						customer_order = createEntity(resultSet);
					}
				}
			}
		}

		return customer_order;
	}

	@Override
	Customer_order createEntity(ResultSet resultSet) throws SQLException {
		Customer_order customer_order = new Customer_order();
		customer_order.setId(resultSet.getInt("customer_order_id"));
		customer_order.setAmount(resultSet.getBigDecimal("amount"));
		customer_order.setCreateDate(resultSet.getDate("date_created"));
		{
			Customer customer = new Customer();
			customer.setId(resultSet.getInt("customer_id"));
			customer.setFirstName(resultSet.getString("first_name"));
			customer.setLastName(resultSet.getString("last_name"));
			customer.setEmail(resultSet.getString("email"));
			customer.setPhone(resultSet.getString("phone_number"));
			customer.setAdress(resultSet.getString("address"));
			customer.setCityRegion(resultSet.getString("city"));
			customer.setCcNumber(resultSet.getString("cc_number"));
			customer_order.setCustomer(customer);
		}
		customer_order.setConfirmation_number(resultSet.getInt("confirmation_number"));
		return customer_order;
	
	}
	
}
